﻿//IMPORTANT: Don't forget to mention that "Visual Studio 2010 Tools for SQL Server Compact 3.5 SP2 ENU" must be installed.
//It should be by default.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlServerCe;
using System.Linq;
using Caffeine.Examples.Contacts.Data;
using Caffeine.Examples.Contacts.Repository;
using Caffeine.Examples.Contacts.ViewModel;
using Microsoft.Practices.Unity;

namespace Caffeine.Examples.Repository
{
    public class ContactRepository : IContactRepository
    {
        #region State

        private readonly IUnityContainer _container;

        #endregion

        #region Constructors

        public ContactRepository(IUnityContainer container)
        {
            _container = container;
        }

        #endregion

        #region Public

        public IUnityContainer Container
        {
            get
            {
                return _container;
            }
        }

        public void SaveContact(ContactViewModel contact)
        {
            IDictionary<String, Object> extensibleData = contact.GetExtensibleData();

            if (extensibleData != null) //then update the contact...
            {
                UpdateContact(contact.FirstName.Value, contact.LastName.Value, (Int32)extensibleData["ContactId"]);
            }
            else //otherwise insert them...
            {
                Int32 contactId = InsertContact(contact.FirstName.Value, contact.LastName.Value);
                contact.SetExtensibleData("ContactId", contactId);
            }
        }

        public void RemoveContact(ContactViewModel contact)
        {
            Int32 contactId = (Int32)contact.GetExtensibleData()["ContactId"];
            DeleteContact(contactId);
        }

        public IEnumerable<AddressViewModel> GetContactAddresses(ContactViewModel contact)
        {
            Int32 contactId = (Int32)contact.GetExtensibleData()["ContactId"];
            List<AddressViewModel> addresses = new List<AddressViewModel>();

            using (ContactEntities entities = new ContactEntities())
            {
                IEnumerable<Address> addressEntities = from entity in entities.Contacts
                                                 where entity.ContactId == contactId
                                                 join address in entities.Addresses on entity.ContactId equals address.ContactId
                                                 select address;

                foreach (Address addressEntity in addressEntities)
                {
                    AddressViewModel address = new AddressViewModel(Container);
                    address.StreetAddress.AssignValueAsCommitted(addressEntity.Street);
                    address.ZipCode.AssignValueAsCommitted(addressEntity.ZipCode);
                    address.SetExtensibleData("AddressId", addressEntity.AddressId);
                    addresses.Add(address);
                }
            }

            return addresses;
        }

        public IEnumerable<ContactViewModel> GetContacts()
        {
            List<ContactViewModel> contacts = new List<ContactViewModel>();

            for (Int32 index = 0; index < 1000; index++)
            {
                //contacts.AddRange(InnerGetContacts());
            }

            return contacts;
        }

        public IEnumerable<ContactViewModel> InnerGetContacts()
        {
            IEnumerable<ContactViewModel> contacts = null;

            using (ContactEntities entities = new ContactEntities())
            {
                contacts = entities.Contacts.Select
                (
                    delegate(Contact entity)
                    {
                        ContactViewModel viewModel = new ContactViewModel(Container);
                        viewModel.FirstName.AssignValueAsCommitted(entity.FirstName);
                        viewModel.LastName.AssignValueAsCommitted(entity.LastName);
                        viewModel.SetExtensibleData("ContactId", entity.ContactId);
                        return viewModel;
                    }
                ).ToList();
            }

            return contacts;
        }

        public void SaveAddress(AddressViewModel address)
        {
            IDictionary<String, Object> extensibleData = address.GetExtensibleData();
            Int32 contactId = (Int32)(address.Parent).GetExtensibleData()["ContactId"];

            if (extensibleData != null) //then update the address...
            {
                Int32 addressId = (Int32)extensibleData["AddressId"];
                UpdateAddress(address.StreetAddress.Value, address.ZipCode.Value, addressId, contactId);
            }
            else //otherwise insert it...
            {
                Int32 addressId = InsertAddress(address.StreetAddress.Value, address.ZipCode.Value, contactId);
                address.SetExtensibleData("AddressId", addressId);
            }
        }

        public void RemoveAddress(AddressViewModel address)
        {
            Int32 addressId = (Int32)address.GetExtensibleData()["AddressId"];
            DeleteAddress(addressId);
        }

        #endregion

        #region Private

        private void UpdateContact(String firstName, String lastName, Int32 contactId)
        {
            using (SqlCeConnection connection = new SqlCeConnection(ConfigurationManager.ConnectionStrings["Contacts"].ConnectionString))
            using (SqlCeCommand command = new SqlCeCommand("UPDATE Contact SET FirstName = @FirstName, LastName = @LastName WHERE ContactId = @ContactId", connection))
            {
                command.Parameters.AddWithValue("FirstName", firstName);
                command.Parameters.AddWithValue("LastName", lastName);
                command.Parameters.AddWithValue("ContactId", contactId);

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }
        }

        private Int32 InsertContact(String firstName, String lastName)
        {
            using (SqlCeConnection connection = new SqlCeConnection(ConfigurationManager.ConnectionStrings["Contacts"].ConnectionString))
            using (SqlCeCommand command = new SqlCeCommand("INSERT INTO Contact(FirstName, LastName) VALUES (@FirstName, @LastName)", connection))
            {
                command.Parameters.AddWithValue("FirstName", firstName);
                command.Parameters.AddWithValue("LastName", lastName);

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }

            Int32 contactId = 0;

            using (ContactEntities contacts = new ContactEntities())
            {
                contactId = contacts.Contacts.Max(contact => contact.ContactId);
            }

            return contactId;
        }

        private void DeleteContact(Int32 contactId)
        {
            using (SqlCeConnection connection = new SqlCeConnection(ConfigurationManager.ConnectionStrings["Contacts"].ConnectionString))
            using (SqlCeCommand command = new SqlCeCommand("DELETE FROM CONTACT WHERE ContactId = @ContactId", connection))
            {
                command.Parameters.AddWithValue("ContactId", contactId);

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }
        }

        private void UpdateAddress(String street, String zipCode, Int32 addressId, Int32 contactId)
        {
            using (SqlCeConnection connection = new SqlCeConnection(ConfigurationManager.ConnectionStrings["Contacts"].ConnectionString))
            using (SqlCeCommand command = new SqlCeCommand("UPDATE ADDRESS SET Street=@Street, ZipCode=@ZipCode WHERE AddressId=@AddressId AND ContactId=@ContactId", connection))
            {
                command.Parameters.AddWithValue("Street", street);
                command.Parameters.AddWithValue("ZipCode", zipCode);
                command.Parameters.AddWithValue("AddressId", addressId);
                command.Parameters.AddWithValue("ContactId", contactId);

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }
        }

        private Int32 InsertAddress(String street, String zipCode, Int32 contactId)
        {
            using (SqlCeConnection connection = new SqlCeConnection(ConfigurationManager.ConnectionStrings["Contacts"].ConnectionString))
            using (SqlCeCommand command = new SqlCeCommand("INSERT INTO ADDRESS(Street, ZipCode, ContactId) VALUES (@Street, @ZipCode, @ContactId)", connection))
            {
                command.Parameters.AddWithValue("Street", street);
                command.Parameters.AddWithValue("ZipCode", zipCode);
                command.Parameters.AddWithValue("ContactId", contactId);

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }

            Int32 addressId = 0;

            using (ContactEntities contacts = new ContactEntities())
            {
                addressId = contacts.Addresses.Max(a => a.AddressId);
            }

            return addressId;
        }

        private void DeleteAddress(Int32 addressId)
        {
            using (SqlCeConnection connection = new SqlCeConnection(ConfigurationManager.ConnectionStrings["Contacts"].ConnectionString))
            using (SqlCeCommand command = new SqlCeCommand("DELETE FROM ADDRESS WHERE AddressId = @AddressId", connection))
            {
                command.Parameters.AddWithValue("AddressId", addressId);

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }
        }

        #endregion
    }
}
